package jsyf.oa.dao.capital;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;

import jsyf.oa.entity.capital.FlowEntity;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;

/**
 * Created by houtailei on 2018/2/25.
 */
@Repository
public class FlowDao extends BaseDao {

    public FlowDao() {
        super.iBaseEntity = new FlowEntity();
    }

    public Integer addFlow(IBaseEntity entity) {
        entity.setId(super.generateKey());
        return super.insert(entity.getTableName(), entity.getJSONObject());
    }

    public List<JSONObject> getFlowList(String condition, Object[] params, int requestPage, int pageSize) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT ");
        sb.append("flow.ID AS id,bank.BANK_NAME AS bankName,flow.MONEY AS money,flow.DEL_FLAG AS delFlag, ");
        sb.append("flow.FLOW_DIRECTION AS flowDirection,flow.PROJECT AS project,people.NAME AS name,flow.CREATE_TIME AS createTime ");
        sb.append("FROM ");
        sb.append("t_cpt_flow AS flow ");
        sb.append("LEFT JOIN t_cpt_bank_card AS bank ON flow.TK_BANK_CARD_ID = bank.ID ");
        sb.append("LEFT JOIN t_sys_user AS people ON flow.CREATE_USER_ID = people.ID ");
        sb.append("WHERE 1=1 ");
        sb.append(condition);
        super.appendPageSql(sb, requestPage, pageSize);
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString(), params);
        return jsonList;
    }
    public Integer getFlowListCount(String condition, Object[] params) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT ");
        sb.append("count(1) as count ");
        sb.append("FROM ");
        sb.append("t_cpt_flow AS flow ");
        sb.append("LEFT JOIN t_cpt_bank_card AS bank ON flow.TK_BANK_CARD_ID = bank.ID ");
        sb.append("LEFT JOIN t_sys_user AS people ON flow.CREATE_USER_ID = people.ID ");
        sb.append("WHERE 1=1 ");
        sb.append(condition);

        String count = super.queryForString(sb.toString(), params);
        return Integer.parseInt(count);
    }
    public List<FlowEntity> getEntityListByCondition(String condition, Object[] params) {
        List<JSONObject> jsonList = getJSONListByCondition(condition, params);
        List<FlowEntity> entityList = new ArrayList<>();
        for (int i = 0; i < jsonList.size(); i++) {
            FlowEntity entity = (FlowEntity) convertJson2Entity(jsonList.get(i));
            entityList.add(entity);
        }
        return entityList;
    }

    public String getbalance(String id,String date){
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT ");
        sb.append("(SELECT INIT_MONEY FROM t_cpt_bank_card WHERE id='"+id+"')+ ");
        sb.append("IFNULL(SUM(CASE WHEN FLOW_DIRECTION=1 THEN money ELSE 0 END),0)- ");
        sb.append("IFNULL(SUM(CASE WHEN FLOW_DIRECTION=0 THEN money ELSE 0 END),0)  AS balance ");
        sb.append("FROM `t_cpt_flow` WHERE TK_BANK_CARD_ID='"+id+"' AND DEL_FLAG=0 AND DATE_FORMAT(CREATE_TIME,'%Y-%m-%d')<=DATE_FORMAT('"+date+"','%Y-%m-%d'); ");

        JSONObject jsonObject = super.queryForJsonList(sb.toString()).get(0);
        String balance = jsonObject.getString("balance");

        return balance;
    }
    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, FlowEntity.class);
    }
}

